# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

import pymysql
import pymysql.cursors

from twisted.enterprise import adbapi


class ScrapyNddPipeline(object):
    def process_item(self, item, spider):
        return item


class ScrapyNddNewUrlPipeline(object):
    """
    异步写入mysql
    """
    def __init__(self, dbpool):
        self.record = set()
        self.dbpool = dbpool

    @classmethod
    def from_settings(cls, settings):
        dbparms = dict(
            host=settings["MYSQL_HOST"],
            db=settings["MYSQL_DBNAME"],
            user=settings["MYSQL_USER"],
            password=settings["MYSQL_PASSWORD"],
            port=settings["MYSQL_PORT"],
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor,
            use_unicode=True
        )
        dbpool = adbapi.ConnectionPool("pymysql", **dbparms)
        return cls(dbpool)

    def process_item(self, item, spider):
        if spider.name == 'ppx_new_url':  # 只处理ppx爬虫数据
            query = self.dbpool.runInteraction(self.do_update, item)
            query.addErrback(self.handle_error, item, spider)
            return item
        else:
            return item

    def handle_error(self, failure, item, spider):
        # 处理异步插入异常
        pass

    def do_update(self, cursor, item):
        query_sql = """
            update main_domain set main_domain = %s
        """
        args = (item.get("new_url", ""),)
        cursor.execute(query_sql, args)


class MysqlTwistedVideoPipeline(object):
    """
    异步写入mysql
    """
    def __init__(self, dbpool):
        self.record = set()
        self.dbpool = dbpool

    @classmethod
    def from_settings(cls, settings):
        dbparms = dict(
            host=settings["MYSQL_HOST"],
            db=settings["MYSQL_DBNAME"],
            user=settings["MYSQL_USER"],
            password=settings["MYSQL_PASSWORD"],
            port=settings["MYSQL_PORT"],
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor,
            use_unicode=True
        )
        dbpool = adbapi.ConnectionPool("pymysql", **dbparms)
        return cls(dbpool)

    def process_item(self, item, spider):
        if spider.name == 'ppx_video':  # 只处理ppx爬虫数据
            video_id = item.get("video_id", 0)
            if video_id in self.record:
                return item
            else:
                self.record.add(video_id)
                # 使用Twisted将mysql插入变成异步执行
                query = self.dbpool.runInteraction(self.do_insert, item)
                query.addErrback(self.handle_error, item, spider)
                return item
        elif spider.name == 'ppx_new_video':
            video_id = item.get("video_id", 0)
            if video_id in self.record:
                return item
            else:
                self.record.add(video_id)
                # 使用Twisted将mysql插入变成异步执行
                query = self.dbpool.runInteraction(self.do_query_or_update, item)
                query.addErrback(self.handle_error, item, spider)
                return item
        else:
            return item

    def handle_error(self, failure, item, spider):
        # 处理异步插入异常
        video_id = item.get("video_id", 0)
        self.record.remove(video_id)

    def do_query_or_update(self, cursor, item):
        query_sql = """
            select * from video where video_id = %s
        """
        args = (int(item.get("video_id", 0)),)
        res = cursor.execute(query_sql, args)
        if res <= 0:
            self.do_insert(cursor, item)
        else:
            # 结束爬虫
            exit(0)
            pass

    def do_insert(self, cursor, item):
        insert_sql = """
            insert into video(video_id, 
            category, 
            type, 
            title, 
            screenshot_url_1, 
            screenshot_url_2, 
            screenshot_url_3, 
            screenshot_url_4, 
            screenshot_url_5, 
            videos_screenshots, 
            video_alt_url, 
            video_url,
            download_url, 
            views, 
            md5, 
            size, 
            duration, 
            status, 
            tag, 
            vip, 
            create_time)
            values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        vip = 0
        if len(item.get("video_alt_url", "")) > 0:
            vip = 1
        args = (int(item.get("video_id", 0)),
                item.get("category", ""),
                item.get("type", ""),
                item.get("title", ""),
                item.get("screenshot_url_1", ""),
                item.get("screenshot_url_2", ""),
                item.get("screenshot_url_3", ""),
                item.get("screenshot_url_4", ""),
                item.get("screenshot_url_5", ""),
                item.get("videos_screenshots", ""),
                item.get("video_alt_url", ""),
                item.get("video_url", ""),
                item.get("download_url", ""),
                int(item.get("views", 0)),
                item.get("md5", ""),
                item.get("size", ""),
                item.get("duration", ""),
                int(item.get("status", -1)),
                item.get("tag", ""),
                vip,
                int(item.get("create_time", 0)))
        cursor.execute(insert_sql, args)


class MysqlTwistedCategoryPipeline(object):
    """
        异步写入mysql
        """

    def __init__(self, dbpool):
        self.record = set()
        self.dbpool = dbpool

    @classmethod
    def from_settings(cls, settings):
        dbparms = dict(
            host=settings["MYSQL_HOST"],
            db=settings["MYSQL_DBNAME"],
            user=settings["MYSQL_USER"],
            password=settings["MYSQL_PASSWORD"],
            port=settings["MYSQL_PORT"],
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor,
            use_unicode=True
        )
        dbpool = adbapi.ConnectionPool("pymysql", **dbparms)
        return cls(dbpool)

    def process_item(self, item, spider):
        if spider.name == 'ppx_category':  # 只处理ppx_category爬虫数据
            item_href = item.get("item_href", 0)
            if item_href in self.record:
                return item
            else:
                self.record.add(item_href)
                # 使用Twisted将mysql插入变成异步执行
                query = self.dbpool.runInteraction(self.do_insert, item)
                query.addErrback(self.handle_error, item, spider)
                return item
        else:
            return item

    def handle_error(self, failure, item, spider):
        # 处理异步插入异常
        item_href = item.get("item_href", 0)
        self.record.remove(item_href)

    def do_insert(self, cursor, item):
        insert_sql = """
                insert into video_category(cate_url, 
                cate_name, 
                cate_count, 
                hoting_rate, 
                item_href, 
                create_time, 
                curr_page, 
                curr_page_item, 
                complete)
                values (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
        args = (item.get("cate_url", ""),
                item.get("cate_name", ""),
                int(item.get("cate_count", 0)),
                int(item.get("hoting_rate", 0)),
                item.get("item_href", ""),
                int(item.get("create_time", 0)),
                int(item.get("curr_page", 0)),
                int(item.get("curr_page_item", 0)),
                int(item.get("complete", 0)))
        cursor.execute(insert_sql, args)
